using System;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// Summary description for LinkManager
/// </summary>
public class LinkManager
{
    #region gets
    public static DataTable GetLinks()
    {
        //List<Link> lisReturnCollection=null;
        DataTable dtaReturnTable = null;

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("GetLinks", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        //Link linTemLink;
                        //lisReturnCollection = new List<Link>();
                        dtaReturnTable = new DataTable();

                        DataRow droTemp;

                        for (int intCounter = 0; intCounter < reader.FieldCount; intCounter++)
                        {
                            dtaReturnTable.Columns.Add(reader.GetName(intCounter));
                        }

                        do
                        {

                            droTemp = dtaReturnTable.NewRow();

                            for (int intCounter = 0; intCounter < reader.FieldCount; intCounter++)
                            {
                                droTemp[reader.GetName(intCounter)] = reader[intCounter];
                            }

                            dtaReturnTable.Rows.Add(droTemp);
                            //linTemLink = new Link();

                            //linTemLink.Alt = reader["LIN_ALT"].ToString();
                            //linTemLink.CategorID = Convert.ToInt32(reader["LIN_LCA_ID"]);
                            //linTemLink.Description = reader["LIN_DESCRIPTION"].ToString();
                            //linTemLink.Display = reader["LIN_DISPLAY"].ToString();
                            //linTemLink.SortOrder = Convert.ToInt32(reader["LIN_SORT"]);
                            //linTemLink.URL = reader["LIN_URL"].ToString();
                            //linTemLink.Category = reader["LCA_NAME"].ToString();
                            //linTemLink.ID = Convert.ToInt32(reader["LIN_ID"]);

                            //lisReturnCollection.Add(linTemLink);

                        } while (reader.Read()) ;// end of the read while loop

                    }// end of the if check to see if anything was found
                }// end reader using              
            }// end command using
        }// end connection using

        //return lisReturnCollection;
        return dtaReturnTable;
    }// end of the GetLinks method

    public static DataTable GetLinksByCategory(int ID)
    {
        //List<Link> lisReturnCollection=null;
        DataTable dtaReturnTable = null;

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("GetLinksByCategory", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@LCA_ID", ID));

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        //Link linTemLink;
                        //lisReturnCollection = new List<Link>();
                        dtaReturnTable = new DataTable();

                        DataRow droTemp;

                        for (int intCounter = 0; intCounter < reader.FieldCount; intCounter++)
                        {
                            dtaReturnTable.Columns.Add(reader.GetName(intCounter));
                        }

                        do
                        {

                            droTemp = dtaReturnTable.NewRow();

                            for (int intCounter = 0; intCounter < reader.FieldCount; intCounter++)
                            {
                                droTemp[reader.GetName(intCounter)] = reader[intCounter];
                            }

                            dtaReturnTable.Rows.Add(droTemp);
                            //linTemLink = new Link();

                            //linTemLink.Alt = reader["LIN_ALT"].ToString();
                            //linTemLink.CategorID = Convert.ToInt32(reader["LIN_LCA_ID"]);
                            //linTemLink.Description = reader["LIN_DESCRIPTION"].ToString();
                            //linTemLink.Display = reader["LIN_DISPLAY"].ToString();
                            //linTemLink.SortOrder = Convert.ToInt32(reader["LIN_SORT"]);
                            //linTemLink.URL = reader["LIN_URL"].ToString();
                            //linTemLink.Category = reader["LCA_NAME"].ToString();
                            //linTemLink.ID = Convert.ToInt32(reader["LIN_ID"]);

                            //lisReturnCollection.Add(linTemLink);

                        } while (reader.Read());// end of the read while loop

                    }// end of the if check to see if anything was found
                }// end reader using              
            }// end command using
        }// end connection using

        //return lisReturnCollection;
        return dtaReturnTable;
    }// end of the GetLinks method

    public static DataTable GetSomeLinks()
    {
        //List<Link> lisReturnCollection = null;
        DataTable dtaReturnTable = null;

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("GetSomeLinks", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        //Link linTemLink;
                       // lisReturnCollection = new List<Link>();
                        dtaReturnTable = new DataTable();

                        DataRow droTemp;

                        for (int intCounter = 0; intCounter < reader.FieldCount; intCounter++)
                        {
                            dtaReturnTable.Columns.Add(reader.GetName(intCounter));
                        }

                        do
                        {

                            droTemp = dtaReturnTable.NewRow();

                            for (int intCounter = 0; intCounter < reader.FieldCount; intCounter++)
                            {
                                droTemp[reader.GetName(intCounter)] = reader[intCounter];
                            }

                            dtaReturnTable.Rows.Add(droTemp);
                            //linTemLink = new Link();

                            //linTemLink.Alt = reader["LIN_ALT"].ToString();
                            //linTemLink.CategorID = Convert.ToInt32(reader["LIN_LCA_ID"]);
                            //linTemLink.Description = reader["LIN_DESCRIPTION"].ToString();
                            //linTemLink.Display = reader["LIN_DISPLAY"].ToString();
                            //linTemLink.SortOrder = Convert.ToInt32(reader["LIN_SORT"]);
                            //linTemLink.URL = reader["LIN_URL"].ToString();
                            //linTemLink.Category = reader["LCA_NAME"].ToString();
                            //linTemLink.ID = Convert.ToInt32(reader["LIN_ID"]);

                            //lisReturnCollection.Add(linTemLink);

                        } while (reader.Read()) ;// end of the read while loop

                    }// end of the if check to see if anything was found
                }// end reader using              
            }// end command using
        }// end connection using

       // return lisReturnCollection;

        return dtaReturnTable;
    }// end of the GetSomeLinks method

    public static Link GetLink(int ID)
    {
        Link linReturnLink = new Link();

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("GetLink", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter ("LIN_ID", ID));

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {  
                        linReturnLink.Alt = reader["LIN_ALT"].ToString();
                        linReturnLink.CategorID = Convert.ToInt32(reader["LIN_LCA_ID"].ToString());
                        linReturnLink.Description = reader["LIN_DESCRIPTION"].ToString();
                        linReturnLink.Display = reader["LIN_DISPLAY"].ToString();
                        linReturnLink.SortOrder = Convert.ToInt32(((reader["LIN_SORT"]==DBNull.Value)?"0":reader["LIN_SORT"]));
                        linReturnLink.URL = reader["LIN_URL"].ToString();
                        linReturnLink.ID = Convert.ToInt32(reader["LIN_ID"]);

                    }// end of the read while loop
                }// end reader using              
            }// end command using
        }// end connection using

        return linReturnLink;
    }// end of the GetLink method

    public static DataTable GetLinkCategories()
    {
        //List<ListItem<int, string, string>> lisReturnCollection = null;
        DataTable dtaReturnTable = null;

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("GetLinkCategories", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        //ListItem<int, string, string> litTemLink;
                        //lisReturnCollection = new List<ListItem<int, string, string>>();
                        dtaReturnTable = new DataTable();

                        DataRow droTemp;

                        for (int intCounter = 0; intCounter < reader.FieldCount; intCounter++)
                        {
                            dtaReturnTable.Columns.Add(reader.GetName(intCounter));
                        }

                        do
                        {

                            droTemp = dtaReturnTable.NewRow();

                            for (int intCounter = 0; intCounter < reader.FieldCount; intCounter++)
                            {
                                droTemp[reader.GetName(intCounter)] = reader[intCounter];
                            }

                            dtaReturnTable.Rows.Add(droTemp);
                            //litTemLink = new ListItem<int, string, string>();
                            
                            //litTemLink.Key = Convert.ToInt32(reader["LCA_ID"].ToString());
                            //litTemLink.Value = reader["LCA_NAME"].ToString();
                            //litTemLink.Description = reader["LCA_DESC"].ToString();

                            //lisReturnCollection.Add(litTemLink);

                        }while (reader.Read()) ;// end of the read while loop

                    } // end of the if check to see if anything was found
                }// end reader using              
            }// end command using
        }// end connection using

        //return lisReturnCollection;
            return dtaReturnTable;
    }// end of the GetLinkCategories method
    #endregion

    #region updates
    public static bool UpdateLink(Link link)
    {
        int intRowsUpdated = 0;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("UpdateLink", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@id", link.ID));
                command.Parameters.Add(new SqlParameter("@Desc", link.Description));
                command.Parameters.Add(new SqlParameter("@CatId", link.CategorID));
                command.Parameters.Add(new SqlParameter("@Alt", link.Alt));
                command.Parameters.Add(new SqlParameter("@Url", link.URL));
                command.Parameters.Add(new SqlParameter("@Display", link.Display));

                connection.Open();
                intRowsUpdated = command.ExecuteNonQuery();


            }// end command using
        }// end connection using

        return ((intRowsUpdated == 0) ? false : true);

    }// end of the UpdateLink method

    public static bool UpdateLinkCategory(int LCA_ID, string LCA_NAME, string LCA_DESC)
    {
        int intRowsUpdated = 0;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("UpdateLinkCategory", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@LCA_ID", LCA_ID));
                command.Parameters.Add(new SqlParameter("@LCA_DESC", LCA_DESC));
                command.Parameters.Add(new SqlParameter("@LCA_NAME", LCA_NAME));
               
                connection.Open();
                intRowsUpdated = command.ExecuteNonQuery();


            }// end command using
        }// end connection using

        return ((intRowsUpdated == 0) ? false : true);

    }// end of the UpdateLinkCategory method
    #endregion

    #region inserts
    public static bool InsertLink(Link link)
    {
        int intRowsUpdated = 0;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("AddLink", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@Desc", link.Description));
                command.Parameters.Add(new SqlParameter("@CatId", link.CategorID));
                command.Parameters.Add(new SqlParameter("@Alt", link.Alt));
                command.Parameters.Add(new SqlParameter("@Url", link.URL));
                command.Parameters.Add(new SqlParameter("@Display", link.Display));


                connection.Open();
                intRowsUpdated = command.ExecuteNonQuery();


            }// end command using
        }// end connection using

        return ((intRowsUpdated == 0) ? false : true);

    }// end of the InsertLink method

    public static bool InsertLinkCategory(string Name, string Desc)
    {
        int intRowsUpdated = 0;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("AddLinkCategory", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@LCA_DESC", Desc));
                command.Parameters.Add(new SqlParameter("@LCA_NAME", Name));


                connection.Open();
                intRowsUpdated = command.ExecuteNonQuery();


            }// end command using
        }// end connection using

        return ((intRowsUpdated == 0) ? false : true);

    }// end of the InsertLinkCategory method
    #endregion

    #region deletes
    public static bool DeleteLinkCategory(int LCA_ID)
    {
        int intRowsUpdated = 0;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("RemoveLinkCategory", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@LCA_ID", LCA_ID));
              
                connection.Open();
                intRowsUpdated = command.ExecuteNonQuery();


            }// end command using
        }// end connection using

        return ((intRowsUpdated == 0) ? false : true);

    }// end of the DeleteLinkCategory method

    public static bool DeleteLink(int LIN_ID )
    {
        int intRowsUpdated = 0;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("RemoveLink", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@LIN_ID", LIN_ID));

                connection.Open();
                intRowsUpdated = command.ExecuteNonQuery();

            }// end command using
        }// end connection using

        return ((intRowsUpdated == 0) ? false : true);

    }// end of the DeleteLink method

    #endregion

}// end of the LinkManager class
